﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 图片墙
    /// </summary>
    public class PartyPhotoWall : EntityBaseOperation
    {
        #region 字段属性
        /// <summary>
        /// 图片库名称
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 所属组织
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        public string Content { get; set; }

        /// <summary>
        /// 类型 （字典）
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 所属专题（字典）
        /// </summary>
        public string Thematic { get; set; }

        /// <summary>
        /// 1 草稿 4发布
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 1审核中  2,审核通过，4，驳回
        /// </summary>
        public int Aduit { get; set; }

        /// <summary>
        /// 0、无；1、置顶；2、固顶；3、置顶+固顶
        /// </summary>
        public int Flags { get; set; }

        /// <summary>
        /// 来源Id（以后用于推送）
        /// </summary>
        public string SourceId { get; set; }

        /// <summary>
        /// 是否已推送
        /// </summary>
        public bool IsPush { get; set; }

        public virtual ICollection<PartyPhotoWallImage> PartyPhotoWallImages { get; set; }
        #endregion

        public static DataTable GetCategory(string ddicType)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select DDicMember,DDicMemberName from tbldatadictionary where DDicType=?DDicType");

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?DDicType",MySqlDbType.VarChar)
            };
            parameters[0].Value = ddicType;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }

        public static DataTable QueryPhotoInfo(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select UserName,BranchName,photo.CreateTime,SourceId,DDicMemberName as Category from tblpartyphotowall photo
left join tblpartyorgmember member on member.LoginID = photo.CreateBy
left join tblpartyorgbranch branch on branch.BranchID = photo.BranchID
left join tbldatadictionary dict on dict.DDicMember = photo.Category and dict.DDicType='ZHDJ.PhotoWall.Category'
where photo.ID=@ID");

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?ID",MySqlDbType.VarChar)
            };
            parameters[0].Value = id;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }

        /// <summary>
        /// 组织中心留影墙
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="title"></param>
        /// <param name="branchId"></param>
        /// <param name="status"></param>
        /// <param name="createBy"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static DataSet GetPartyPhotoWallData(int pageIndex, int pageSize, string title, string branchId, string status, string createBy, string category)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  photo.*,UserName
from tblpartyphotowall photo
left join tblpartyorgmember m on photo.CreateBy=m.LoginID
where 1=1");

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and photo.Title like ?key ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", title)
                    });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and photo.BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = string.Format("{0}", branchId)
                    });
            }
            if (!string.IsNullOrEmpty(status))  //发布状态
            {
                sb.Append(" and photo.Status=?Status ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Status",
                        Value = string.Format("{0}", status)
                    });
            }
            if (!string.IsNullOrEmpty(category))  //图片类型
            {
                sb.Append(" and Category=?Category ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Category",
                        Value = string.Format("{0}", category)
                    });
            }
            if (!string.IsNullOrEmpty(createBy))  //创建人
            {
                //sb.Append(" and photo.CreateBy like ?CreateBy or UserName like ?CreateBy");
                sb.Append(" and UserName like ?CreateBy");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "CreateBy",
                        Value = string.Format("%{0}%", createBy)
                    });
            }

            sb.Append(" order by Status desc, photo.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static string QueryFirstImageUrl(string id)
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sb.Append(@"SELECT FileID FROM tblpartyphotowallimage WHERE PhotoWallID=?PhotoWallID ORDER BY CreateTime DESC LIMIT 0,1");
            parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "PhotoWallID",
                        Value = id
                    });

            object fileid = MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray()); ;
            if (fileid != null)
                return Convert.ToString(fileid);
            else
                return "";
        }

        public static DataSet GetPartyPhotoWallTitle(string branchId, string countFilter = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            sb.Append("select SQL_CALC_FOUND_ROWS  * from tblpartyphotowall where 1=1");

            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=@BranchID)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = branchId
                    });
            }

            sb.Append(" and Status=4");  //已发布
            sb.AppendFormat(" order by CreateTime desc {0};SELECT FOUND_ROWS();", countFilter);

            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
    }
}
